import MySQLdb
import mysql
from flask_mysqldb import MySQL
from flask import Flask, jsonify, send_file
import qrcode
import base64
import json
import os
from openai import OpenAI
from flask import *

from api import api_key
import logging
import random
import string
from functools import wraps

# 配置日志(数据库约束)
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)

app = Flask(__name__)
app.secret_key = 'your_secret_key'  # 设置一个安全的密钥

# 配置 MySQL 数据库
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'QWEzxc200348'
app.config['MYSQL_DB'] = 'trace_back'

mysql = MySQL(app)

# 添加角色验证装饰器
def role_required(allowed_roles):
    def decorator(f):
        @wraps(f)
        def decorated_function(*args, **kwargs):
            if 'role' not in session:
                flash('请先登录')
                return redirect(url_for('login'))
            
            if session['role'] not in allowed_roles:
                flash('您没有权限访问此页面')
                return redirect(url_for('index'))
                
            return f(*args, **kwargs)
        return decorated_function
    return decorator

@app.route('/')
@app.route("/auth")
def auth():
    return render_template("auth.html")


@app.route('/')
@app.route('/index')
@role_required(['admin', 'consumer', 'producer', 'transporter'])
def index():
    if 'username' not in session:
        return redirect(url_for('login'))
    
    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            # 获取用户信息
            cursor.execute('''
                SELECT id, username, role, contact_info, organization 
                FROM users 
                WHERE username = %s
            ''', (session['username'],))
            user = cursor.fetchone()
            
            # 获取合约统计信息
            cursor.execute('''
                SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN status = '通过' THEN 1 ELSE 0 END) as approved
                FROM contracts
            ''')
            contract_stats = cursor.fetchone()
            
            if user:
                session['role'] = user['role']
            
            return render_template('index.html', user=user, contract_stats=contract_stats)
            
    except Exception as e:
        print(f"数据库错误: {e}")
        return render_template('index.html', user=None, contract_stats={'total': 0, 'approved': 0})


@app.route("/")
@app.route("/trace_select")
@role_required(['admin', 'consumer', 'producer', 'transporter'])
def trace_select():
    return render_template("溯源查询.html")


@app.route("/")
@app.route("/change_form")
@role_required(['admin', 'consumer', 'producer', 'transporter'])
def change_form():
    if 'username' not in session:
        return redirect(url_for('login'))

    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            cursor.execute('SELECT id, username, role, contact_info, organization FROM users WHERE username = %s',
                         (session['username'],))
            user = cursor.fetchone()

            if not user:
                flash('用户信息不存在')
                return redirect(url_for('login'))

            return render_template('资料编辑.html', user=user)

    except Exception as e:
        flash('获取用户信息失败: ' + str(e))
        return render_template('资料编辑.html', user={
            'id': '',
            'username': session['username'],
            'role': session['role'],
            'contact_info': '',
            'organization': ''
        })


@app.route("/")
@app.route("/change_passwd")
@role_required(['admin', 'consumer', 'producer', 'transporter'])
def change_passwd():
    return render_template("修改密码.html")


@app.route("/my_user")
@role_required(['admin'])
def my_user():
    if 'username' not in session:
        return redirect(url_for('login'))

    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            cursor.execute('''
                SELECT id, username, contact_info, role, organization 
                FROM users
            ''')
            users = cursor.fetchall()
            return render_template("我的用户.html", users=users)

    except Exception as e:
        flash('获取用户列表失败: ' + str(e))
        return render_template("我的用户.html", users=[])


@app.route("/add_user", methods=['GET', 'POST'])
@role_required(['admin'])
def add_user():
    if 'username' not in session:
        return redirect(url_for('login'))

    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        role = request.form.get('role')

        if not all([username, password, role]):
            return jsonify({'error': '请填写所有必填字段'}), 400

        try:
            with mysql.connection.cursor() as cursor:
                # 检查用户名是否已存在
                cursor.execute('SELECT id FROM users WHERE username = %s', (username,))
                if cursor.fetchone():
                    return jsonify({'error': '用户名已存在'}), 400

                # 插入新用户
                cursor.execute('''
                    INSERT INTO users (username, password, role) 
                    VALUES (%s, %s, %s)
                ''', (username, password, role))
                mysql.connection.commit()

                return jsonify({'message': '添加成功'})

        except Exception as e:
            return jsonify({'error': str(e)}), 500

    return render_template("添加用户.html")


@app.route("/")
@app.route("/transport_test")
@role_required(['admin', 'transporter'])
def transport_test():
    return render_template("运输管理.html")


#运输信息传递

@app.route('/transport_info', methods=['GET'])
def transport_info():
    cursor = mysql.connection.cursor()
    try:
        # 查询运输信息，并包含外键关联的产品名称、运输负责人和地址信息
        cursor.execute("""
            SELECT 
                t.id AS transport_id,
                p.name AS product_name,
                t.vehicle_info,
                a1.province AS start_province,
                a1.city AS start_city,
                a1.street AS start_street,
                a1.postal_code AS start_postal_code,
                a2.province AS end_province,
                a2.city AS end_city,
                a2.street AS end_street,
                a2.postal_code AS end_postal_code,
                t.start_time,
                t.end_time,
                t.temperature,
                u.username AS transporter,
                t.distance_km
            FROM 
                transport t
            JOIN 
                products p ON t.product_id = p.id
            JOIN 
                addresses a1 ON t.start_address_id = a1.id
            JOIN 
                addresses a2 ON t.end_address_id = a2.id
            JOIN 
                users u ON t.transporter_id = u.id
        """)
        transport_info_list = cursor.fetchall()

        # 将查询结果转换为字典列表，以便 jsonify 可以处理
        transport_info = []
        for info in transport_info_list:
            transport_info.append({
                'transport_id': info[0],
                'product_name': info[1],
                'vehicle_info': info[2],
                'start_address': {
                    'province': info[3],
                    'city': info[4],
                    'street': info[5],
                    'postal_code': info[6]
                },
                'end_address': {
                    'province': info[7],
                    'city': info[8],
                    'street': info[9],
                    'postal_code': info[10]
                },
                'start_time': info[11].isoformat() if info[11] else None,
                'end_time': info[12].isoformat() if info[12] else None,
                'temperature': info[13],
                'transporter': info[14],
                'distance_km': info[15]
            })

        return jsonify(transport_info)
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    finally:
        cursor.close()

@app.route('/route')
def route():
    start_city = request.args.get('start_city')
    start_street = request.args.get('start_street')
    end_city = request.args.get('end_city')
    end_street = request.args.get('end_street')

    # 你可以在这里处理这些参数，例如进行路线规划
    # 为了简化示例，我们直接重定向到一个新的页面并显示这些参数
    return render_template('运输路线图.html',
                           start_city=start_city,
                           start_street=start_street,
                           end_city=end_city,
                           end_street=end_street)

@app.route('/products', methods=['GET'])
def get_products():
    cursor = mysql.connection.cursor()
    cursor.execute("SELECT id, name FROM products")
    products = cursor.fetchall()
    cursor.close()
    return jsonify([{'id': product[0], 'name': product[1]} for product in products])


@app.route('/users', methods=['GET'])
def get_users():
    cursor = mysql.connection.cursor()
    cursor.execute("SELECT id, username FROM users")
    users = cursor.fetchall()
    cursor.close()
    return jsonify([{'id': user[0], 'username': user[1]} for user in users])


@app.route('/add_transport_info', methods=['POST'])
def add_transport_info():
    data = request.get_json()
    cursor = mysql.connection.cursor()
    try:
        # 插入地址信息
        # 插入起点地址信息
        cursor.execute("INSERT INTO addresses (province, city, street, postal_code) VALUES (%s, %s, %s, %s)",
                       (data['start_province'], data['start_city'], data['start_street'], data['start_postal_code']))
        start_address_id = cursor.lastrowid

        # 插入终点地址信息
        cursor.execute("INSERT INTO addresses (province, city, street, postal_code) VALUES (%s, %s, %s, %s)",
                       (data['end_province'], data['end_city'], data['end_street'], data['end_postal_code']))
        end_address_id = cursor.lastrowid

        # 插入运输信息
        cursor.execute(
            "INSERT INTO transport (product_id, vehicle_info, start_address_id, end_address_id, distance_km, start_time, end_time, temperature, transporter_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
            (data['product_name'], data['vehicle_info'], start_address_id, end_address_id, data['distance_km'],
             data['start_time'], data['end_time'], data['temperature'], data['transporter']))
        mysql.connection.commit()
        cursor.close()
        return jsonify({'status': 'success'})
    except Exception as e:
        mysql.connection.rollback()
        cursor.close()
        return jsonify({'error': str(e)}), 500


@app.route("/")
@app.route("/transport_show")
@role_required(['admin', 'transporter'])
def transport_show():
    return render_template("运输路线图.html")


@app.route("/")
@app.route("/product_manage")
@role_required(['admin'])
def product_manage():
    if 'username' not in session:
        return redirect(url_for('login'))

    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            cursor.execute('''
                SELECT id, title, content, sender_id, receiver_id, 
                       DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') as created_at, 
                       status
                FROM contracts
                ORDER BY created_at DESC
            ''')
            contracts = cursor.fetchall()
            return render_template("合约管理.html", contracts=contracts)

    except Exception as e:
        flash('获取合约列表失败: ' + str(e))
        return render_template("合约管理.html", contracts=[])


@app.route("/login", methods=['GET', 'POST'])
def login():
    msg = ''
    if request.method == 'POST' and 'username' in request.form and 'password' in request.form:
        username = request.form['username']
        password = request.form['password']

        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            cursor.execute('SELECT * FROM users WHERE username = %s', (username,))
            user = cursor.fetchone()

        if user and user['password'] == password:
            # 将用户信息存储到会话中
            session['loggedin'] = True
            session['username'] = user['username']
            session['role'] = user['role']
            session['user_info'] = {
                'username': user['username'],
                'role': user['role'],
                'contact_info': user['contact_info'],
                'organization': user['organization']
            }
            return redirect(url_for('index'))
        else:
            msg = "用户名或密码错误"
            flash(msg)

    return render_template('auth.html', msg=msg)


@app.route("/register", methods=['GET', 'POST'])
def register():
    msg = ''
    if request.method == 'POST' and 'username' in request.form and 'password' in request.form:
        username = request.form['username']
        password = request.form['password']

        with mysql.connection.cursor() as cursor:
            try:
                cursor.execute('INSERT INTO users (username, password) VALUES (%s, %s)', (username, password))
                mysql.connection.commit()
                flash('Registration successful!')
                return redirect(url_for('login'))
            except MySQLdb.IntegrityError as e:
                msg = "注册用户已存在"
                flash(msg)

    return render_template('auth.html', msg=msg, active_tab='register')


# 原材料录入
@app.route('/raw_material', methods=['GET', 'POST'])
@role_required(['admin', 'producer'])
def raw_material():
    msg = ''
    if request.method == 'POST':
        material_name = request.form.get('name')
        source = request.form.get('source')
        seed_or_breed_source = request.form.get('seed_or_breed_source')
        purchase_time = request.form.get('purchase_time')
        batch_number = request.form.get('batch_number')
        quality_report = request.form.get('quality_report')

        if not all([material_name, source, seed_or_breed_source, purchase_time, batch_number, quality_report]):
            flash('请填写所有必填字段！')
        else:
            try:
                cursor = mysql.connection.cursor()
                cursor.execute(
                    'INSERT INTO materials (name, source, seed_or_breed_source, purchase_time, batch_number, quality_report) VALUES (%s, %s, %s, %s, %s, %s)',
                    (material_name, source, seed_or_breed_source, purchase_time, batch_number, quality_report))
                mysql.connection.commit()
                cursor.close()
                flash('原材料录入成功!')
                return redirect(url_for('raw_material'))
            except MySQLdb.IntegrityError as e:
                msg = '原材料录入失败: {}'.format(str(e))
                logger.error(msg)
                flash(msg)
    return render_template('原材料录入.html', msg=msg)


# 生产记录创建
@app.route('/production_record', methods=['GET', 'POST'])
@role_required(['admin', 'producer'])
def production_record():
    msg = ''
    if request.method == 'POST':
        material_id = request.form.get('material_id')
        process_time = request.form.get('process_time')
        process_location = request.form.get('process_location')
        process_equipment = request.form.get('process_equipment')
        operator_name = request.form.get('operator_name')
        process_details = request.form.get('process_details')
        operator_id = request.form.get('operator_id')

        if not all([material_id, process_time, process_location, process_equipment, operator_name, process_details,
                    operator_id]):
            flash('请填写所有必填字段！')
        else:
            try:
                cursor = mysql.connection.cursor()
                cursor.execute(
                    'INSERT INTO production (material_id, process_time, process_location, process_equipment, operator_name, process_details, operator_id) VALUES (%s, %s, %s, %s, %s, %s, %s)',
                    (material_id, process_time, process_location, process_equipment, operator_name, process_details,
                     operator_id))
                mysql.connection.commit()
                cursor.close()
                flash('生产记录创建成功!')
                return redirect(url_for('production_record'))
            except MySQLdb.IntegrityError as e:
                msg = '生产记录创建失败: {}'.format(str(e))
                logger.error(msg)
                flash(msg)
    return render_template('生产记录创建.html', msg=msg)


# 产品生成
@app.route('/product_generation', methods=['GET', 'POST'])
@role_required(['admin', 'producer'])
def product_generation():
    msg = ''
    if request.method == 'POST':
        product_name = request.form.get('product_name')
        material_id = request.form.get('material_id')
        production_id = request.form.get('production_id')

        if not all([product_name, material_id, production_id]):
            flash('请填写所有必填字段！')
        else:
            try:
                # 生成随机唯一标识（12位数字和字母的组合）
                unique_code = ''.join(random.choices(string.ascii_uppercase + string.digits, k=12))

                cursor = mysql.connection.cursor()
                cursor.execute(
                    'INSERT INTO products (name, material_id, unique_code, production_id) VALUES (%s, %s, %s, %s)',
                    (product_name, material_id, unique_code, production_id))
                mysql.connection.commit()
                cursor.close()
                flash(f'产品生成成功! 唯一标识码: {unique_code}')
                return redirect(url_for('product_generation'))
            except MySQLdb.IntegrityError as e:
                msg = '产品生成失败: {}'.format(str(e))
                logger.error(msg)
                flash(msg)
    return render_template('产品信息录入.html', msg=msg)


# 默认首页，包含原材料录入、生产记录创建和产品生成链接


client = OpenAI(
    api_key=api_key,
    base_url="https://open.bigmodel.cn/api/paas/v4/"
)


@app.route('/')
@app.route('/AI')
@role_required(['admin', 'consumer', 'producer', 'transporter'])
def AI():
    return render_template('AI.html')


@app.route('/chat', methods=['POST'])
def chat():
    user_message = request.json.get('message')

    def generate():
        try:
            response = client.chat.completions.create(
                model="glm-4-0520",  # 填写需要调用的模型编码
                messages=[
                    {"role": "user", "content": user_message},
                ],
                stream=True,
            )
            for chunk in response:
                content = chunk.choices[0].delta.content
                if content:  # 确保有内容输出
                    yield f"data: {json.dumps({'response': content})}\n\n"
        except Exception as e:
            yield f"data: {json.dumps({'error': str(e)})}\n\n"

    return Response(generate(), mimetype='text/event-stream')


# 123

@app.route('/query_trace', methods=['POST'])
def query_trace():
    trace_code = request.json.get('trace_code')

    if not trace_code:
        return jsonify({'error': '请输入溯源码'}), 400

    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            # 查询产品基本信息和原料信息
            cursor.execute('''
                SELECT 
                    p.unique_code,
                    p.name as product_name,
                    m.name as material_name,
                    m.source,
                    m.seed_or_breed_source,
                    m.purchase_time,
                    m.batch_number,
                    m.quality_report,
                    pr.process_time,
                    pr.process_location,
                    pr.process_equipment,
                    pr.operator_name,
                    pr.process_details
                FROM products p
                JOIN materials m ON p.material_id = m.id
                LEFT JOIN production pr ON p.production_id = pr.id
                WHERE p.unique_code = %s
            ''', (trace_code,))

            result = cursor.fetchone()

            if not result:
                return jsonify({'error': '未找到该溯源码对应的产品信息'}), 404

            # 查询运输信息
            cursor.execute('''
                SELECT 
                    t.vehicle_info,
                    CONCAT(a1.province, a1.city, a1.street, ' → ', 
                           a2.province, a2.city, a2.street) as route,
                    t.start_time,
                    t.end_time,
                    t.temperature as temperature_control
                FROM transport t
                JOIN products p ON t.product_id = p.id 
                JOIN addresses a1 ON t.start_address_id = a1.id
                JOIN addresses a2 ON t.end_address_id = a2.id
                WHERE p.unique_code = %s
                ORDER BY t.start_time ASC
            ''', (trace_code,))

            transports = cursor.fetchall()

            # 查询验证记录
            cursor.execute('''
                SELECT verification_time, verification_result
                FROM verificationlogs v
                JOIN products p ON v.product_id = p.id
                WHERE p.unique_code = %s
                ORDER BY verification_time DESC
            ''', (trace_code,))

            verifications = cursor.fetchall()

            # 组合返回数据
            response_data = {
                'product_info': {
                    'product_name': result['product_name'],
                    'material_name': result['material_name'],
                    'source': result['source'],
                    'seed_or_breed_source': result['seed_or_breed_source'],
                    'purchase_time': result['purchase_time'].strftime('%Y-%m-%d %H:%M:%S') if result[
                        'purchase_time'] else None,
                    'batch_number': result['batch_number'],
                    'quality_report': result['quality_report']
                },
                'production_info': {
                    'process_time': result['process_time'].strftime('%Y-%m-%d %H:%M:%S') if result[
                        'process_time'] else None,
                    'process_location': result['process_location'],
                    'process_equipment': result['process_equipment'],
                    'operator_name': result['operator_name'],
                    'process_details': result['process_details']
                },
                'transport_info': [
                    {
                        'vehicle_info': t['vehicle_info'],
                        'route': t['route'],
                        'start_time': t['start_time'].strftime('%Y-%m-%d %H:%M:%S'),
                        'end_time': t['end_time'].strftime('%Y-%m-%d %H:%M:%S') if t['end_time'] else None,
                        'temperature_control': f"{t['temperature_control']}°C" if t[
                                                                                      'temperature_control'] is not None else '-'
                    } for t in transports
                ],
                'verifications': [
                    {
                        'time': v['verification_time'].strftime('%Y-%m-%d %H:%M:%S'),
                        'result': v['verification_result']
                    } for v in verifications
                ]
            }

            return jsonify(response_data)

    except Exception as e:
        return jsonify({'error': str(e)}), 500


APP_ID = 'wx28327b50e68d4db5'
APP_SECRET = '79687e7c5cc51a0cfa1527ed3e602d28'


@app.route('/generate_qrcode', methods=['POST'])
def generate_qrcode():
    # trace_code = 'SZRP20240101003'  # 直接设置 trace_code
    trace_code = request.json.get('trace_code')

    if not trace_code:
        return jsonify({'error': '请输入溯源码'}), 400

    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            # 查询产品基本信息和原料信息
            cursor.execute('''
                    SELECT 
                        p.unique_code,
                        p.name as product_name,
                        m.name as material_name,
                        m.source,
                        pr.process_time,
                        pr.process_location,
                        pr.process_equipment,
                        pr.operator_name,
                        pr.process_details
                    FROM products p
                    JOIN materials m ON p.material_id = m.id
                    LEFT JOIN production pr ON p.production_id = pr.id
                    WHERE p.unique_code = %s
                ''', (trace_code,))

            result = cursor.fetchone()

            if not result:
                return jsonify({'error': '未找到该溯源码对应的产品信息'}), 404

            data = {
                'product_info': {
                    'unique_code': result['unique_code'],
                    'product_name': result['product_name'],
                    'material_name': result['material_name'],
                    'source': result['source']
                },
                'production_info': {
                    'process_time': result['process_time'].strftime('%Y-%m-%d %H:%M:%S') if result[
                        'process_time'] else None,
                    'process_location': result['process_location'],
                    'process_equipment': result['process_equipment'],
                    'operator_name': result['operator_name'],
                    'process_details': result['process_details']
                }
            }

        # 编码数据
        encoded_data = base64.b64encode(json.dumps(data).encode('utf-8')).decode('utf-8')

        # 生成二维码
        qr = qrcode.QRCode(
            version=None,  # 动态调整版本号
            error_correction=qrcode.constants.ERROR_CORRECT_H,  # 使用高错误纠正级别
            box_size=6,  # 减少 box_size
            border=2,  # 减少 border 大小
        )
        qr.add_data(f"http://127.0.0.1:5000/pages/index/index?data={encoded_data}")
        qr.make(fit=True)

        img_path = 'static/qrcode.png'
        img = qr.make_image(fill_color="black", back_color="white")
        img.save(img_path)

        qrcode_url = url_for('static', filename='qrcode.png')
        return jsonify({'qrcode_url': qrcode_url})
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# @app.route('/generate_qrcode2')
# def get_product_info():
#     trace_code = 'SZRP20240101003'  # 直接设置 trace_code
#     # trace_code = request.json.get('trace_code')
#
#     if not trace_code:
#         return jsonify({'error': '请输入溯源码'}), 400
#     try:
#         with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
#             # 查询产品基本信息和原料信息
#             cursor.execute('''
#                 SELECT
#                     p.unique_code,
#                     p.name as product_name,
#                     m.name as material_name,
#                     m.source,
#                     m.seed_or_breed_source,
#                     m.purchase_time,
#                     m.batch_number,
#                     m.quality_report,
#                     pr.process_time,
#                     pr.process_location,
#                     pr.process_equipment,
#                     pr.operator_name,
#                     pr.process_details
#                 FROM products p
#                 JOIN materials m ON p.material_id = m.id
#                 LEFT JOIN production pr ON p.production_id = pr.id
#                 WHERE p.unique_code = %s
#             ''', (trace_code,))
#
#             result = cursor.fetchone()
#
#             if not result:
#                 return "未找到该溯源码对应的产品信息", 404
#
#             # 构建文本输出
#             output = f"产品名称: {result['product_name']}\n"
#             output += f"原材料名称: {result['material_name']}\n"
#             output += f"来源: {result['source']}\n"
#             output += f"种子/品种来源: {result['seed_or_breed_source']}\n"
#             output += f"采购时间: {result['purchase_time'].strftime('%Y-%m-%d %H:%M:%S') if result['purchase_time'] else '无'}\n"
#             output += f"批次号: {result['batch_number']}\n"
#             output += f"质量报告: {result['quality_report']}\n\n"
#
#             if result['process_time']:
#                 output += f"加工时间: {result['process_time'].strftime('%Y-%m-%d %H:%M:%S')}\n"
#                 output += f"加工地点: {result['process_location']}\n"
#                 output += f"加工设备: {result['process_equipment']}\n"
#                 output += f"操作员姓名: {result['operator_name']}\n"
#                 output += f"加工详情: {result['process_details']}\n\n"
#
#             # 查询验证记录
#             cursor.execute('''
#                 SELECT verification_time, verification_result
#                 FROM verificationlogs v
#                 JOIN products p ON v.product_id = p.id
#                 WHERE p.unique_code = %s
#                 ORDER BY verification_time DESC
#             ''', (trace_code,))
#
#             verifications = cursor.fetchall()
#             for v in verifications:
#                 output += f"验证时间: {v['verification_time'].strftime('%Y-%m-%d %H:%M:%S')}\n"
#                 output += f"验证结果: {v['verification_result']}\n\n"
#
#                 # 编码数据
#             encoded_data = base64.b64encode(output.encode('utf-8')).decode('utf-8')
#
#             # 生成二维码
#             qr = qrcode.QRCode(
#                 version=None,  # 动态调整版本号
#                 error_correction=qrcode.constants.ERROR_CORRECT_H,  # 使用高错误纠正级别
#                 box_size=6,  # 减少 box_size
#                 border=2,  # 减少 border 大小
#             )
#             qr.add_data(f"http://127.0.0.1:5000/pages/index/index?data={encoded_data}")
#             qr.make(fit=True)
#
#             img_path = 'static/qrcode2.png'
#             img = qr.make_image(fill_color="black", back_color="white")
#             img.save(img_path)
#
#             qrcode_url = url_for('static', filename='qrcode.png')
#             return jsonify({'qrcode_url': qrcode_url})
#
#     except Exception as e:
#         return jsonify({'error': str(e)}), 500

@app.route('/update_profile', methods=['POST'])
def update_profile():
    if 'username' not in session:
        return redirect(url_for('login'))

    contact_info = request.form.get('contact_info')
    organization = request.form.get('organization')

    try:
        with mysql.connection.cursor() as cursor:
            cursor.execute('''
                UPDATE users 
                SET contact_info = %s, organization = %s 
                WHERE username = %s
            ''', (contact_info, organization, session['username']))
            mysql.connection.commit()
            flash('资料更新成功!')

    except Exception as e:
        flash('资料更新失败: ' + str(e))

    return redirect(url_for('change_form'))


@app.route('/update_password', methods=['POST'])
def update_password():
    if 'username' not in session:
        flash('请先登录')
        return redirect(url_for('login'))

    old_password = request.form.get('old_password')
    new_password = request.form.get('new_password')
    confirm_password = request.form.get('confirm_password')
    username = session['username']

    # 验证新密码是否一致
    if new_password != confirm_password:
        flash('两次输入的新密码不一致')
        return redirect(url_for('change_passwd'))

    try:
        with mysql.connection.cursor() as cursor:
            # 验证原密码是否正确
            cursor.execute('SELECT password FROM users WHERE username = %s', (username,))
            user = cursor.fetchone()

            if not user or user[0] != old_password:
                flash('原密码错误')
                return redirect(url_for('change_passwd'))

            # 更新密码
            cursor.execute('''
                UPDATE users 
                SET password = %s 
                WHERE username = %s
            ''', (new_password, username))
            mysql.connection.commit()
            flash('密码修改成功!')

    except Exception as e:
        flash('密码修改失败: ' + str(e))

    return redirect(url_for('change_passwd'))


@app.route("/edit_user/<username>", methods=['GET', 'POST'])
@role_required(['admin'])
def edit_user(username):
    if 'username' not in session:
        return redirect(url_for('login'))

    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            if request.method == 'POST':
                # 获取表单数据
                contact_info = request.form.get('contact_info')
                password = request.form.get('password')
                organization = request.form.get('organization')
                role = request.form.get('role')

                # 构建更新SQL
                update_fields = ['contact_info = %s', 'organization = %s', 'role = %s']
                params = [contact_info, organization, role]

                # 如果提供了新密码则更新密码
                if password:
                    update_fields.append('password = %s')
                    params.append(password)

                params.append(username)  # WHERE条件的参数

                # 执行更新
                sql = 'UPDATE users SET ' + ', '.join(update_fields) + ' WHERE username = %s'
                cursor.execute(sql, params)
                mysql.connection.commit()
                flash('用户信息更新成功!')
                return redirect(url_for('my_user'))

            # GET请求时获取用户信息
            cursor.execute('SELECT * FROM users WHERE username = %s', (username,))
            user = cursor.fetchone()

            if not user:
                flash('用户不存在')
                return redirect(url_for('my_user'))

            return jsonify({
                'username': user['username'],
                'contact_info': user['contact_info'],
                'organization': user['organization'],
                'role': user['role']
            })

    except Exception as e:
        flash('操作失败: ' + str(e))
        return redirect(url_for('my_user'))


@app.route("/delete_user/<username>", methods=['POST'])
@role_required(['admin'])
def delete_user(username):
    if 'username' not in session:
        return jsonify({'error': '请先登录'}), 401

    try:
        with mysql.connection.cursor() as cursor:
            # 检查是否为最后一个管理员
            cursor.execute('SELECT COUNT(*) FROM users WHERE role = "admin"')
            admin_count = cursor.fetchone()[0]

            cursor.execute('SELECT role FROM users WHERE username = %s', (username,))
            user_role = cursor.fetchone()

            if user_role and user_role[0] == 'admin' and admin_count <= 1:
                return jsonify({'error': '不能删除最后一个管理员账号!'}), 400

            # 执行删除操作
            cursor.execute('DELETE FROM users WHERE username = %s', (username,))
            mysql.connection.commit()

            if cursor.rowcount == 0:
                return jsonify({'error': '用户不存在'}), 404

            return jsonify({'message': '删除成功'})

    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route("/search_users")
def search_users():
    if 'username' not in session:
        return jsonify({'error': '请先登录'}), 401

    contact_info = request.args.get('contact_info', '')
    username = request.args.get('username', '')
    role = request.args.get('role', '')

    # 获取分页参数
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 5))  # 每页显示5条记录

    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            # 先获取总记录数
            count_query = 'SELECT COUNT(*) as total FROM users WHERE 1=1'
            params = []

            if contact_info:
                count_query += ' AND contact_info LIKE %s'
                params.append(f'%{contact_info}%')
            if username:
                count_query += ' AND username LIKE %s'
                params.append(f'%{username}%')
            if role:
                count_query += ' AND role = %s'
                params.append(role)

            cursor.execute(count_query, params)
            total = cursor.fetchone()['total']

            # 计算总页数
            total_pages = (total + per_page - 1) // per_page

            # 获取分页数据
            query = '''
                SELECT id, username, contact_info, role, organization 
                FROM users 
                WHERE 1=1
            '''

            if contact_info:
                query += ' AND contact_info LIKE %s'
            if username:
                query += ' AND username LIKE %s'
            if role:
                query += ' AND role = %s'

            query += ' LIMIT %s OFFSET %s'
            params.extend([per_page, (page - 1) * per_page])

            cursor.execute(query, params)
            users = cursor.fetchall()

            return jsonify({
                'users': users,
                'pagination': {
                    'total': total,
                    'per_page': per_page,
                    'current_page': page,
                    'total_pages': total_pages
                }
            })

    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route("/toggle_contract_status/<int:contract_id>", methods=['GET'])
@role_required(['admin'])
def toggle_contract_status(contract_id):
    if 'username' not in session:
        return redirect(url_for('login'))

    try:
        with mysql.connection.cursor() as cursor:
            # 先获取当前状态
            cursor.execute('SELECT status FROM contracts WHERE id = %s', (contract_id,))
            current_status = cursor.fetchone()[0]

            # 切换状态
            new_status = '未通过' if current_status == '通过' else '通过'

            # 更新数据库
            cursor.execute('UPDATE contracts SET status = %s WHERE id = %s',
                           (new_status, contract_id))
            mysql.connection.commit()

        return redirect(url_for('product_manage'))

    except Exception as e:
        flash('更新合约状态失败: ' + str(e))
        return redirect(url_for('product_manage'))


@app.route("/data_analysis")
@role_required(['admin'])
def data_analysis():
    return render_template("数据分析.html")


@app.route("/api/visualization_data")
def visualization_data():
    try:
        with mysql.connection.cursor(cursorclass=MySQLdb.cursors.DictCursor) as cursor:
            # 获取产品分布数据 - 修改为从products表获取实际数据
            cursor.execute('''
                SELECT p.name, COUNT(*) as count 
                FROM products p 
                JOIN materials m ON p.material_id = m.id
                GROUP BY p.name
            ''')
            product_distribution = [
                {"name": row["name"], "value": int(row["count"])}
                for row in cursor.fetchall()
            ]

            # 修改温度监控数据查询
            cursor.execute('''
                SELECT 
                    DATE_FORMAT(t.start_time, '%Y-%m-%d %H:%i') as time,
                    t.temperature,
                    t.vehicle_info,
                    CONCAT(a1.city, '-', a2.city) as route
                FROM transport t
                JOIN addresses a1 ON t.start_address_id = a1.id
                JOIN addresses a2 ON t.end_address_id = a2.id
                ORDER BY t.start_time DESC
                LIMIT 20
            ''')
            temp_data = cursor.fetchall()
            temperature_data = {
                "times": [row["time"] for row in temp_data],
                "values": [float(row["temperature"]) for row in temp_data],
                "vehicles": [row["vehicle_info"] for row in temp_data],
                "routes": [row["route"] for row in temp_data]
            }

            # 进一步简化原材料来源分析的查询
            cursor.execute('''
                SELECT 
                    m.source as supplier,
                    m.name as material_name,
                    COUNT(p.id) as usage_count
                FROM materials m
                LEFT JOIN products p ON m.id = p.material_id
                GROUP BY m.source, m.name
                ORDER BY m.source, usage_count DESC
            ''')

            materials = cursor.fetchall()

            # 重组数据结构
            suppliers = {}
            for m in materials:
                if m['supplier'] not in suppliers:
                    suppliers[m['supplier']] = {
                        'name': m['supplier'],
                        'children': []
                    }
                suppliers[m['supplier']]['children'].append({
                    'name': m['material_name'],
                    'value': m['usage_count']
                })

            material_tree = list(suppliers.values())

            # 获取运输距离数据 - 添加更多统计信息
            cursor.execute('''
                SELECT 
                    CONCAT(a1.city, '-', a2.city) as route,
                    AVG(t.distance_km) as avg_distance,
                    COUNT(*) as trip_count,
                    MIN(t.temperature) as min_temp,
                    MAX(t.temperature) as max_temp
                FROM transport t
                JOIN addresses a1 ON t.start_address_id = a1.id
                JOIN addresses a2 ON t.end_address_id = a2.id
                GROUP BY route
                ORDER BY avg_distance DESC
                LIMIT 10
            ''')
            transport_data = cursor.fetchall()
            transport_stats = {
                "routes": [row["route"] for row in transport_data],
                "distances": [float(row["avg_distance"]) for row in transport_data],
                "tripCounts": [int(row["trip_count"]) for row in transport_data],
                "temperatures": [{
                    "min": float(row["min_temp"]),
                    "max": float(row["max_temp"])
                } for row in transport_data]
            }

            # 获取验证记录统计
            cursor.execute('''
                SELECT 
                    p.name as product_name,
                    COUNT(v.id) as verification_count
                FROM products p
                LEFT JOIN verificationlogs v ON p.id = v.product_id
                GROUP BY p.name
                ORDER BY verification_count DESC
            ''')
            verification_data = cursor.fetchall()
            verification_stats = {
                "products": [row["product_name"] for row in verification_data],
                "counts": [int(row["verification_count"]) for row in verification_data]
            }

            # 获取合约状态统计
            cursor.execute('''
                SELECT 
                    status,
                    COUNT(*) as count
                FROM contracts
                GROUP BY status
            ''')
            contract_stats = cursor.fetchall()

            return jsonify({
                "productDistribution": product_distribution,
                "temperatureData": temperature_data,
                "materialSources": material_tree,
                "transportData": transport_stats,
                "verificationStats": verification_stats,
                "contractStats": contract_stats
            })

    except Exception as e:
        print(f"Error in visualization_data: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.context_processor
def inject_user():
    if 'user_info' in session:
        return {'user': session['user_info']}
    return {'user': None}


@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))


@app.route('/add_contract', methods=['POST'])
@role_required(['admin'])
def add_contract():
    try:
        data = request.get_json()
        
        # 验证必填字段
        required_fields = ['title', 'content', 'sender_id', 'receiver_id']
        for field in required_fields:
            if not data.get(field):
                return jsonify({'success': False, 'error': f'缺少必填字段: {field}'})
        
        with mysql.connection.cursor() as cursor:
            # 验证发送方和接收方ID是否存在
            cursor.execute('SELECT id FROM users WHERE id IN (%s, %s)', 
                         (data['sender_id'], data['receiver_id']))
            if cursor.rowcount != 2:
                return jsonify({'success': False, 'error': '发送方或接收方ID不存在'})
            
            # 插入新合约
            cursor.execute('''
                INSERT INTO contracts (title, content, sender_id, receiver_id, status) 
                VALUES (%s, %s, %s, %s, %s)
            ''', (data['title'], data['content'], data['sender_id'], 
                 data['receiver_id'], '未通过'))
            
            mysql.connection.commit()
            
        return jsonify({'success': True})
        
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})


if __name__ == '__main__':
    app.run(debug=True)
